set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

------------------------------------
--用途：分页存储过程(对有主键的表效率极高)  
--说明：(通用)
------------------------------------

ALTER PROCEDURE [dbo].[M_GetListPage]
    @tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 主键字段名
	@fldList      varchar(500),       -- 字段列表
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @strWhere     varchar(1000) = '', -- 查询条件 (注意: 不要加 where)
    @strOrder	  varchar(100) = ''  -- 排序字段，可多个字段排序
AS
declare @strSQL   varchar(2000)       -- 主语句
declare @strTmp   varchar(500)        -- 临时变量
--declare @strOrder varchar(400)        -- 排序类型

--if @OrderType != 0
--begin
--    set @strTmp = '<(select min'
--    --set @strOrder = ' order by [' + @fldName +'] desc'
--end
--else
--begin
--    set @strTmp = '>(select max'
--    --set @strOrder = ' order by [' + @fldName +'] asc'
--end
--暂时取消分页
--set @PageIndex=1
--暂时取消分页
if @strOrder!=''
	begin
		set @strOrder=' order by ' + @strOrder
	end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldList + ' from '
    + @tblName + ' where [' + @fldName + ']' + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
    + @fldName + '] from ' + @tblName + '' + @strOrder + ')'
    + @strOrder

if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldList + ' from '
        + @tblName + ' where [' + @fldName + ']' + ' not in (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
        + @fldName + '] from ' + @tblName + ' where ' + @strWhere + ' '
        + @strOrder + ') and ' + @strWhere + ' ' + @strOrder

print @strSQL
if @PageIndex = 1
begin
    set @strTmp =''
    if @strWhere != ''
        set @strTmp = ' where ' + @strWhere

    set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldList + ' from '
        + @tblName + '' + @strTmp + ' ' + @strOrder
end

--if @IsReCount != 0
    --set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
print @strSQL
exec (@strSQL)






